package com.shujia

import org.junit.{Before, Test}

import scala.io.Source

class Code50Homework1 {
  /**
   * 作业：求出学生偏科比较严重的学生前10名学生
   * 分析：
   *    1. 需要加载数据  学生基本信息  学生成绩数据  科目的总分数据
   *       2. 偏科： 150:140 150:30 =>方差 => (当前分数 - 平均分（自己所有科目的平均分）)**2
   *       3. 由于不同科目之间，总分不一样，那么需要将 考试分数除以科目总分得到科目占比 (归一化处理)，消除量纲
   *       4. 计算完方差后，对方差进行排名
   */

  var scoreList: List[(String, String, Int)] = _

  var stuList: List[(String, String, Int, String, String)] = _

  var objectList: Map[String, Int] = _

  @Before
  def loadData(): Unit = {
    scoreList = Source
      .fromFile("data/score.txt")
      .getLines()
      // 将读取到的每一行数据转换成List中的元素
      .toList
      .map(
        line => {
          val column: Array[String] = line.split(",")
          (column(0), column(1), column(2).toInt)
        }
      )

    stuList = Source
      .fromFile("data/students.txt")
      .getLines()
      // 将读取到的每一行数据转换成List中的元素
      .toList
      // 将每一行的数据进行切分，并包装
      .map(
        (line: String) => {
          val colum: Array[String] = line.split(",")
          // (id,name,age,gender,clazz)
          //  Tuple相比较Array 其元素的数据类型可以多样
          (colum(0), colum(1), colum(2).toInt, colum(3), colum(4))
        }
      )

    objectList = Source
      .fromFile("data/object.txt")
      .getLines()
      // 将读取到的每一行数据转换成List中的元素
      .toList
      // 将每一行的数据进行切分，并包装
      .map(
        (line: String) => {
          val colum: Array[String] = line.split(",")
          // (id,name,age,gender,clazz)
          //  Tuple相比较Array 其元素的数据类型可以多样
          (colum(0), colum(1).toInt)
        }
      ).toMap

  }


  @Test
  def compute(): Unit = {

    // 1.获取成绩数据和科目数据 并进行关联 关联后对成绩数据消除量纲
    val newScore: List[(String, String, Double)] = scoreList
      .filter {
        case (id, courseID, score) => objectList.contains(courseID)
      }
      .map {
        case (id, courseID, score) => {
          val ScoreNum: Int = objectList(courseID)
          (id, courseID, score / ScoreNum.toDouble)
        }
      }

    // 2.计算方差
    val computeRes: Map[String, Double] = newScore
      // 2.1对学生分组，求每个学生的平均分
      .groupBy(_._1)
      .map {
        case (id, oneStuList) => {
          // 拿总分除以大小
          val avg: Double = oneStuList.map(_._3).sum / oneStuList.size

          val oneStuRes: Double = oneStuList
            .map {
              case (id, courseID, score) => {
                (score - avg) * (score - avg)
              }
            }
            .sum

          (id, oneStuRes)
        }
      }

    stuList
      .filter(x => computeRes.contains(x._1))
      .map {
        case (id, name, age, gender, clazz) => {
          val res: Double = computeRes(id)
          (id, name, age, gender, clazz,res)
        }
      }
      .groupBy(_._5)
      .flatMap {
        case (clazz,oneClazzStu) => {
          var pm:Int = 0
          oneClazzStu.sortBy(-_._6).map{
            case (id, name, age, gender, clazz,res) => {
              pm += 1
              (id, name, age, gender, clazz,res,pm)
            }
          }.take(10)
        }
      }
      .toList
      .foreach(println)

    /**
     * score : id, courseID, score
     *
     * info :id, name, age, gender, clazz
     *
     * course : courseID, num
     *
     * with compute_res_tbl as (
     * SELECT
     * T1.*
     * ,(T1.score /T2.num) res
     * FROM score T1 JOIN course T2 ON T1.courseID = T2.courseID
     * )
     *
     * ,sum_diff_res_tbl as(
     * SELECT
     * TTT1.id
     * ,sum(diff_res)  as sum_diff_res
     * FROM(
     * SELECT
     * T1.id
     * ,(TT2.res - TT1.avg_res)*(TT2.res - TT1.avg_res) as diff_res
     * FROM (
     * SELECT
     * T1.id
     * ,avg(res) as avg_res
     * FROM compute_res_tbl T1
     * GROUP BY T1.id
     * )TT1 JOIN compute_res_tbl TT2 ON TT1.id = TT2.id
     * )TTT1 group by TTT1.id
     * )
     *
     * SELECT
     * TT1.*
     * FROM (
     * SELECT
     * T2.*
     * ,T1.sum_diff_res
     * ,row_number() over(partition by T2.clazz order by T1.sum_diff_res desc) as pm
     * FROM sum_diff_res_tbl T1 JOIN info T2 ON T1.id = T2.id
     * )TT1 WHERE TT1.pm <= 10
     *
     */


  }
}
